package com.chris.homecare.data
{
	import com.chris.homecare.utils.FileDownloader;
	
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.filesystem.File;
	import flash.globalization.DateTimeFormatter;
	
	import feathers.data.HierarchicalCollection;
	import feathers.data.ListCollection;

	public class DataUtil
	{
		private static var sqlConnection:SQLConnection;
		
		public function DataUtil()
		{
		}
		
		public static function createDB():void
		{
			sqlConnection = new SQLConnection();
			sqlConnection.open(File.applicationStorageDirectory.resolvePath("HomeCare.sqlite"));
			
			var query:String;
			
			query = "CREATE TABLE IF NOT EXISTS t_business (" +
				"f_business_id VARCHAR(10) NOT NULL PRIMARY KEY, " +
				"f_business_heading VARCHAR(50) NOT NULL, " +
				"f_business_content VARCHAR(1000) NULL, " +
				"f_modifieddt DATE NULL)";
			executeQueryStatement(query);
			
			query = "CREATE TABLE IF NOT EXISTS t_employee (" +
				"f_employee_id VARCHAR(10) NOT NULL PRIMARY KEY, " +
				"f_name VARCHAR(50) NOT NULL, " +
				"f_position_group_id VARCHAR(10) NOT NULL, " +
				"f_position VARCHAR(50) NOT NULL, " +
				"f_image_path VARCHAR(150) NULL, " +
				"f_experience VARCHAR(50) NOT NULL, " +
				"f_rating VARCHAR(10) NOT NULL, " +
				"f_modifieddt DATE NULL)";
			executeQueryStatement(query);
			
			query = "CREATE TABLE IF NOT EXISTS t_job_position_group (" +
				"f_position_group_id VARCHAR(10) NOT NULL PRIMARY KEY, " +
				"f_position_group VARCHAR(50) NOT NULL, " +
				"f_modifieddt DATE NULL) ";
			executeQueryStatement(query);
			
			query = "CREATE TABLE IF NOT EXISTS t_settings (" +
				"f_setting_id VARCHAR(10) NOT NULL PRIMARY KEY, " +
				"f_user_email VARCHAR(255)  NULL, " +
				"f_isregistered_user INTEGER  NULL, " +
				"f_dbversion double NULL, " +
				"f_modifieddt DATE NULL)";
			executeQueryStatement(query);
			
			query = "CREATE TABLE IF NOT EXISTS t_reviews (" +
				"f_review_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
				"f_review_type VARCHAR(50) NOT NULL, " +
				"f_employee_id VARCHAR(10) NOT NULL, " +
				"f_name VARCHAR(50) NOT NULL, " +
				"f_title VARCHAR(50) NOT NULL, " +
				"f_review VARCHAR(1000) NOT NULL, " +
				"f_rating INTEGER NULL, " +
				"f_modifieddt DATE NULL)";
			executeQueryStatement(query);
			
			query = "CREATE TABLE IF NOT EXISTS t_ourservices (" +
				"f_service_id VARCHAR(10) NOT NULL PRIMARY KEY, " +
				"f_heading VARCHAR(50) NOT NULL, " +
				"f_content VARCHAR(1000) NOT NULL, " +
				"f_modifieddt DATE NULL)";
			executeQueryStatement(query);
			
			query = "CREATE TABLE IF NOT EXISTS t_updates (" +
				"f_update_id VARCHAR(10) NOT NULL PRIMARY KEY, " +
				"f_heading VARCHAR(50) NOT NULL, " +
				"f_content VARCHAR(1000) NOT NULL, " +
				"f_name VARCHAR(50) NOT NULL, " +
				"f_name_title VARCHAR(50) NOT NULL, " +
				"f_image_path VARCHAR(150) NULL, " +
				"f_posted_date DATE NULL, "+
				"f_modifieddt DATE NULL)";
			executeQueryStatement(query);
			
			query = "CREATE TABLE IF NOT EXISTS t_event_types (" +
				"f_event_type_id VARCHAR(10) NOT NULL PRIMARY KEY, " +
				"f_event_type VARCHAR(50) NOT NULL, " +
				"f_event_count INTEGER  NULL, " +
				"f_modifieddt DATE NULL)";
			executeQueryStatement(query);
			
			query = "CREATE TABLE IF NOT EXISTS t_events (" +
				"f_event_id VARCHAR(10) NOT NULL PRIMARY KEY, " +
				"f_event_type_id VARCHAR(10) NOT NULL, " +
				"f_event_name VARCHAR(50) NOT NULL, " +
				"f_event_date DATE NOT NULL, " +
				"f_event_location VARCHAR(50) NOT NULL, " +
				"f_event_organiser VARCHAR(50) NOT NULL, " +
				"f_event_data VARCHAR(1000) NOT NULL, " +
				"f_modifieddt DATE NULL)";
			executeQueryStatement(query);
			
			query = "CREATE TABLE IF NOT EXISTS t_jobs_postings (" +
				"f_job_id VARCHAR(10) NOT NULL PRIMARY KEY, " +
				"f_job_title VARCHAR(50) NOT NULL, " +
				"f_job_type VARCHAR(50) NOT NULL, " +
				"f_job_exp VARCHAR(50) NOT NULL, " +
				"f_job_description VARCHAR(1000) NOT NULL)";
			executeQueryStatement(query);
			
			checkAndUpdateDB();
		}
		
		private static function checkAndUpdateDB():void
		{
			var currentversion:Number = 0;
			
			var query:String = "SELECT f_dbversion from t_settings";
			var result:SQLResult = executeQueryStatement(query);
			
			if( result && result.data)
			{
				currentversion = result.data[0].f_dbversion;
			}
			
			if(currentversion < 0.4)
			{
				/*insertJobPositions(FetchLocalData.fetchJobPosition());
				//insertEmployees(FetchLocalData.fetchEmployees());
				insertReviews(FetchLocalData.fetchReviewData());
				insertOurServices(FetchLocalData.fetchOurServicesData());*/
				
				query = "INSERT OR REPLACE INTO t_settings(f_setting_id, f_dbversion, f_modifieddt) VALUES('1', 0.4, julianday('now','localtime'))";
				executeQueryStatement(query);
			}
		}
		
		public static function executeQueryStatement(query:String):SQLResult
		{
			try
			{
				var stmt:SQLStatement = new SQLStatement();
				stmt.sqlConnection = sqlConnection;
				stmt.text = query;
				stmt.execute();
				var result:SQLResult = stmt.getResult();
			}
			
			catch (e:Error)
			{
				trace("EXCEPTION THROWN: "+e);
				trace(e.getStackTrace());
			}
			
			return result;
		}
		
		public static function updateSettings(userEmail:String = ""):void
		{
			var query:String = "UPDATE t_settings SET f_user_email = '" +userEmail+ "', f_isregistered_user = " +1+ " WHERE f_setting_id = '1'";
			executeQueryStatement(query);
		}
		
		public static function insertBusinessData(id:String, heading:String, content:String, modDt:String):void
		{
			var query:String;
			query = "DELETE FROM t_business";
			executeQueryStatement(query);
			
			query = "INSERT INTO t_business(f_business_id, f_business_heading, f_business_content, f_modifieddt) " +
				"VALUES ('" +id+ "', '" +heading+ "', '" +content+ "', " +modDt+ ")";
			executeQueryStatement(query);
		}
		
		public static function insertJobPositions(positionArray:Array):void
		{
			var query:String;
			query = "DELETE FROM t_job_position_group";
			executeQueryStatement(query);
			
			for each (var positionObjet:Object in positionArray)
			{
				query = "INSERT INTO t_job_position_group(f_position_group_id, f_position_group, f_modifieddt) VALUES ('" +positionObjet.positionGroupId+ "', '" +positionObjet.positionGroup+ "', " +positionObjet.modDat+ ")";
				executeQueryStatement(query);
			}
		}
		
		public static function insertEmployees(employeeArray:Array):void
		{
			var query:String;
			
			for each (var employeeObjet:Object in employeeArray)
			{
				query = "INSERT INTO t_employee(f_employee_id, f_name, f_position_group_id, f_position, f_image_path, f_modifieddt) " +
					"VALUES('" +employeeObjet.id+ "', '" +employeeObjet.name+ "', '" +employeeObjet.positionGroupId+ "', '" +employeeObjet.position+ "', '" +employeeObjet.imagePath+ "', " +employeeObjet.modDat+ ")";
				executeQueryStatement(query);
				
			}
		}
		
		public static function checkAndInsertEmployees(employeesArray:Array):void
		{
			var query:String = "SELECT f_employee_id, f_modifieddt FROM t_employee";
			var result:SQLResult = executeQueryStatement(query);
			
			
			for each (var employeeObj:Object in result.data)
			{
				for each(var currentEmployee:Object in employeesArray)
				{
					if(currentEmployee.uid === employeeObj.f_employee_id)
					{
						var dateString:String = currentEmployee.lastupdate;
						dateString = dateString.split("-").join("/");
						var newDate:Date = new Date(Date.parse(dateString));
						if(newDate.toString() === employeeObj.f_modifieddt.toString())
						{
							employeesArray.splice(employeesArray.indexOf(currentEmployee),1);
							break;
						}
					}
				}
			}
			
			for each(var empObj:Object in employeesArray)
			{
				var employee:Employee = new Employee(empObj.uid, empObj.first_name+ " " +empObj.last_name, empObj.occupations, empObj.role_id, empObj.userProfile, empObj.position_by, empObj.lastupdate, empObj.exprience);
				var fileDownloader:FileDownloader = new FileDownloader(employee.imageUrl, empObj.image, employee);
			}
		}
		
		public static function checkAndInsertUpdates(updatesArray:Array):void
		{
			var query:String = "SELECT f_update_id, f_modifieddt FROM t_updates";
			var result:SQLResult = executeQueryStatement(query);
			
			
			for each (var updateObj:Object in result.data)
			{
				for each(var currentEmployee:Object in updatesArray)
				{
					if(currentEmployee.blog_id === updateObj.f_update_id)
					{
						var dateString:String = currentEmployee.lastupdate;
						dateString = dateString.split("-").join("/");
						var newDate:Date = new Date(Date.parse(dateString));
						trace("currentUpdate dateStr"+newDate.toDateString());
						trace("currentUpdate db date"+updateObj.f_modifieddt.toDateString());
						if(newDate.toDateString() === updateObj.f_modifieddt.toDateString())
						{
							updatesArray.splice(updatesArray.indexOf(currentEmployee),1);
							break;
						}
					}
				}
			}
			
			for each(var updatesObj:Object in updatesArray)
			{
				var update:Update = new Update(updatesObj.blog_id, updatesObj.blog_title, updatesObj.blog_desc, updatesObj.posted_by, "", null, updatesObj.userProfile, updatesObj.lastupdate, updatesObj.posted_on);
				var fileDownloader:FileDownloader = new FileDownloader(updatesObj.userProfile, updatesObj.blog_image, update);
			}
		}
		
		public static function insertAnEmployee(employee:Object):void
		{
			var query:String;
			query = "INSERT OR REPLACE INTO t_employee(f_employee_id, f_name, f_position_group_id, f_position, f_image_path, f_rating, f_experience, f_modifieddt) " +
				"VALUES('" +employee.id+ "', '" +employee.name+ "', '" +employee.positionGroupId+ "', '" +employee.position+ "', '" +employee.imageUrl+ "', '" +employee.ratingNumber+ "', '" +employee.exprience+ "', '" +employee.modifiedDt+ "')";
			executeQueryStatement(query);
		}
		
		public static function insertAnUpdate(update:Update):void
		{
			var query:String;
			query = "INSERT OR REPLACE INTO t_updates(f_update_id, f_heading, f_content, f_name, f_name_title,f_image_path, f_posted_date, f_modifieddt) " +
				"VALUES('" +update.id+ "', '" +update.headingText+ "', '" +update.description+ "', '" +update.name+ "', '" +update.nameTitle+ "', '" +update.imageUrl+ "', STRFTIME('%Y-%m-%d',  '"+update.dateString+ "'), '"+update.dateString+ "')";
			executeQueryStatement(query);
		}
		
		private static function insertReviews(reviewArray:Array):void
		{
			var query:String;
			
			for each (var reviewObjet:Object in reviewArray)
			{
				query = "INSERT INTO t_reviews(f_review_id, f_review_type, f_employee_id, f_name, f_title, f_review, f_rating, f_modifieddt) " +
					"VALUES('" +reviewObjet.id+ "', '" +reviewObjet.reviewType+ "', '" +reviewObjet.empId+ "', '" +reviewObjet.name+ "', '" +reviewObjet.nameTitle+ "', '" +reviewObjet.review+ "', '" +reviewObjet.rating+ "', " +reviewObjet.modDat+ ")";
				executeQueryStatement(query);
			}
		}
		
		public static function insertOurServices(servicesArray:Array):void
		{
			var query:String;
			query = "DELETE FROM t_ourservices";
			executeQueryStatement(query);
			
			for each (var serviceObjet:Object in servicesArray)
			{
				query = "INSERT INTO t_ourservices(f_service_id, f_heading, f_content) VALUES ('" +serviceObjet.id+ "', '" +serviceObjet.heading+ "', '" +serviceObjet.content+ "')";
				executeQueryStatement(query);
			}
		}
		
		public static function insertEventCategory(evtCategoryArray:Array):void
		{
			var query:String;
			query = "DELETE FROM t_event_types";
			executeQueryStatement(query);
			
			//var eventCateogyArray:Array = FetchLocalData.fetchEventCategory();
			for each(var eventCategoy:Object in evtCategoryArray)
			{
				query = "INSERT OR REPLACE INTO t_event_types(f_event_type_id, f_event_type, f_event_count, f_modifieddt) " +
					"VALUES('" +eventCategoy.id+ "', '" +eventCategoy.category+  "', '" +eventCategoy.eventCount+"', strftime('%Y-%m-%d', '" +eventCategoy.modDt+ "'))";
				executeQueryStatement(query);
			}
		}
		
		public static function insertEvents(evtsArray:Array):void
		{
			var query:String;
			query = "DELETE FROM t_events";
			executeQueryStatement(query);
			//var eventArray:Array = FetchLocalData.fetchEvents();
			
			for each(var event:Object in evtsArray)
			{
				query = "INSERT OR REPLACE INTO t_events(f_event_id, f_event_type_id, f_event_name, f_event_date, f_event_location, f_event_organiser, f_event_data) " +
					"VALUES('" +event.id+ "', '" +event.typeId+ "', '" +event.name+ "', '" +event.eventDt+ 
					"', '" +event.location+ "', '" +event.organiser+ "', '" +event.content+	"')";
				executeQueryStatement(query);
			}
		}
		
		public static function insertJobPostingsData(jobsArray:Array):void
		{
			var query:String;
			query = "DELETE FROM t_jobs_postings";
			executeQueryStatement(query);
			
			for each(var job:Object in jobsArray)
			{
				query = "INSERT OR REPLACE INTO t_jobs_postings(f_job_id, f_job_title, f_job_type, f_job_exp, f_job_description) " +
					"VALUES('" +job.job_id+ "', '" +job.job_title+ "', '" +job.qualification+ "', ' ', '" +job.job_desc+ "')";
				executeQueryStatement(query);
			}
		}
		
		public static function insertReviewData(reviewArray:Array):void
		{
			var query:String;
			query = "DELETE FROM t_reviews";
			executeQueryStatement(query);
			
			for each(var review:Object in reviewArray)
			{
				query = "INSERT OR REPLACE INTO t_reviews(f_review_type, f_employee_id, f_name, f_title, f_review, f_rating) " +
					"VALUES('" +review.postedType+ "', '" +review.reviewer_id+ "', '" +review.postedUser+ "', '" +review.postedPosition+ "', '" +review.review_desc+ "', '" +review.rate_num+ "')";
				executeQueryStatement(query);
			} 
		}
		
		public static function getBusinessData():Object
		{
			var query:String = "SELECT f_business_heading, f_business_content FROM t_business";
			var result:SQLResult = executeQueryStatement(query);
			
			var obj:Object = new Object();

			if(!result.data || result.data.length == 0) 
			{
				obj.heading = "";
				obj.content = "";
			}
			else
			{
				obj.heading = result.data[0].f_business_heading;
				obj.content = result.data[0].f_business_content;
			}
			
			return obj;
		}
		
		public static function getAllEmployees():HierarchicalCollection
		{
			var query:String = "SELECT emp.f_employee_id, jps.f_position_group_id, emp.f_position, jps.f_position_group, emp.f_name, emp.f_rating, emp.f_image_path, emp.f_experience, emp.f_experience FROM t_employee AS emp LEFT JOIN t_job_position_group AS jps WHERE emp.f_position_group_id = jps.f_position_group_id";
			var result:SQLResult = executeQueryStatement(query);
			
			var positionGroupIds:Array = new Array();
			var teamArray:Array = new Array();
			
			for each (var empObj:Object in result.data)
			{
				var employee:Employee = new Employee(empObj.f_employee_id, empObj.f_name, empObj.f_position, "", empObj.f_image_path, empObj.f_rating,"", empObj.f_experience);
				var index:int = positionGroupIds.indexOf(empObj.f_position_group_id);
				
				if(index == -1)
				{
					positionGroupIds.push(empObj.f_position_group_id);
					var team:Team = new Team(empObj.f_position_group, [employee]);
					teamArray.push(team);
				}
				
				else
				{
					Team(teamArray[index]).children.push(employee);
				}
			}
			
			var teamHierarchicalCollection:HierarchicalCollection = new HierarchicalCollection(teamArray);
			return teamHierarchicalCollection;
		}
		
		public static function getAllReviewsForEmployee(employeeId:String, reviewType:String):ListCollection
		{
			var query:String = "SELECT * FROM t_reviews WHERE f_employee_id = '" +employeeId+ "' AND f_review_type = '" +reviewType+ "'";
			var result:SQLResult = executeQueryStatement(query);
			
			var reviews:Array = new Array();
			
			for each (var reviewObj:Object in result.data)
			{
				var reviewData:ReviewData = new ReviewData(reviewObj.f_review, reviewObj.f_rating, reviewObj.f_name, reviewObj.f_title);
				reviews.push(reviewData);
			}
			
			var ratingListCollection:ListCollection = new ListCollection(reviews);
			return ratingListCollection;
		}
		
		public static function getAllServices():ListCollection
		{
			var query:String = "SELECT * FROM t_ourservices";
			var result:SQLResult = executeQueryStatement(query);
			
			var services:Array = new Array();
			
			for each (var reviewObj:Object in result.data)
			{
				var serviceData:Services = new Services(reviewObj.f_heading, reviewObj.f_content);
				services.push(serviceData);
			}
			
			var servicesListCollection:ListCollection = new ListCollection(services);
			return servicesListCollection;
		}
		
		public static function getAllUpdates():ListCollection
		{
			var query:String = "SELECT f_update_id, f_heading, f_name, f_name_title, f_posted_date, f_modifieddt FROM t_updates";
			var result:SQLResult = executeQueryStatement(query);
			var updates:Array = new Array();
			
			for each (var updatesObj:Object in result.data)
			{
				updates.push(new Update(updatesObj.f_update_id, updatesObj.f_heading, " ", updatesObj.f_name, updatesObj.f_name_title, updatesObj.f_posted_date, "", updatesObj.f_posted_date));
			}
			var updatesListCollection:ListCollection = new ListCollection(updates);
			return updatesListCollection;
		}
		
		public static function getUpdateForId(id:String):Update
		{
			var query:String = "SELECT f_update_id, f_heading, f_content, f_name, f_name_title, f_image_path, f_posted_date, f_modifieddt FROM t_updates WHERE f_update_id ='" +id+ "'";
			var result:SQLResult = executeQueryStatement(query);
			
			var updateObj:Object = result.data[0];
			
			return new Update(updateObj.f_update_id, updateObj.f_heading, updateObj.f_content, updateObj.f_name, updateObj.f_name_title, updateObj.f_posted_date, updateObj.f_image_path, updateObj.f_modifieddt);
		}
		
		public static function getAllEventsCategory():ListCollection
		{
			var date:Date = new Date();
			var newDate:Date = new Date();
			newDate.setDate(date.getDate() + 10);
			
			var formatter:DateTimeFormatter = new DateTimeFormatter("en-US");
			formatter.setDateTimePattern("yyyy-MM-dd");
			
			var dateStr:String = formatter.format(date);
			var newDateStr:String = formatter.format(newDate);
			
			var query:String = "SELECT f_event_type_id, f_event_type, f_event_count FROM t_event_types";
			var result:SQLResult = executeQueryStatement(query);
			
			var eventsCateoryArray:Array = new Array();
			
			for each (var categoryObj:Object in result.data)
			{
				eventsCateoryArray.push({id:categoryObj.f_event_type_id, header:categoryObj.f_event_type, count: categoryObj.f_event_count});
			}
			
			var eventsCategoryListCollection:ListCollection = new ListCollection(eventsCateoryArray);
			return eventsCategoryListCollection;
		}
		
		public static function getAllEventsFortheDay(date:Date):Array
		{
			var formatter:DateTimeFormatter = new DateTimeFormatter("en-US");
			formatter.setDateTimePattern("yyyy-MM-dd");
			var dateStr:String = formatter.format(date);
			var query:String = "SELECT f_event_name, f_event_data, et.f_event_type, f_event_organiser, f_event_location, f_event_date from t_events as evt INNER JOIN t_event_types as et on evt.f_event_type_id = et.f_event_type_id WHERE STRFTIME('%Y-%m-%d', f_event_date) =  STRFTIME('%Y-%m-%d', '" +dateStr+ "')";
			var result:SQLResult = executeQueryStatement(query);
			
			var eventsArray:Array = new Array();
			
			for each (var eventObj:Object in result.data)
			{
				eventsArray.push(new EventData(eventObj.f_event_name, eventObj.f_event_date, eventObj.f_event_type, eventObj.f_event_location, eventObj.f_event_organiser, eventObj.f_event_data));
			}
			
			return eventsArray;
		}
		
		public static function getAllEventsDatesForTheMonth(firstDate:Date, lastDate:Date):Array
		{
			var formatter:DateTimeFormatter = new DateTimeFormatter("en-US");
			formatter.setDateTimePattern("yyyy-MM-dd");
			
			var firstDateStr:String = formatter.format(firstDate);
			var lastDateStr:String = formatter.format(lastDate);
			
			var query:String = "SELECT  strftime('%d', f_event_date) as eventDate from t_events WHERE " +
				"STRFTIME('%Y-%m-%d', f_event_date) >=  STRFTIME('%Y-%m-%d', '" +firstDateStr+ "') AND " +
				"STRFTIME('%Y-%m-%d', f_event_date) <=  STRFTIME('%Y-%m-%d', '" +lastDateStr+ "')";
			var result:SQLResult = executeQueryStatement(query);
			
			return result.data;
		}
		
		public static function getAllEventsForCategory(categoryId:String):ListCollection
		{
			var date:Date = new Date();
			var newDate:Date = new Date();
			newDate.setDate(date.getDate() + 10);
			
			var formatter:DateTimeFormatter = new DateTimeFormatter("en-US");
			formatter.setDateTimePattern("yyyy-MM-dd");
			
			var dateStr:String = formatter.format(date);
			var newDateStr:String = formatter.format(newDate);
			
			var query:String = "SELECT f_event_name, f_event_data, et.f_event_type, f_event_organiser, f_event_location, f_event_date from " +
				"t_events as evt INNER JOIN t_event_types as et on evt.f_event_type_id = et.f_event_type_id WHERE et.f_event_type_id = '" +categoryId+ 
				"' AND STRFTIME('%Y-%m-%d', f_event_date) >=  STRFTIME('%Y-%m-%d', '" +dateStr+ "') AND " +
				"STRFTIME('%Y-%m-%d', f_event_date) <=  STRFTIME('%Y-%m-%d', '" +newDateStr+ "')";
			var result:SQLResult = executeQueryStatement(query);
			
			var eventsArray:Array = new Array();
			for each (var eventObj:Object in result.data)
			{
				eventsArray.push(new EventData(eventObj.f_event_name, eventObj.f_event_date, eventObj.f_event_type, eventObj.f_event_location, eventObj.f_event_organiser, eventObj.f_event_data));
			}
			
			return (new ListCollection(eventsArray));
		}
		
		public static function getNextTenDaysEvents(date:Date):ListCollection
		{
			var newDate:Date = new Date(date.fullYear, date.month, date.date);
			newDate.setDate(newDate.getDate() + 10);
			
			var formatter:DateTimeFormatter = new DateTimeFormatter("en-US");
			formatter.setDateTimePattern("yyyy-MM-dd");
			
			var dateStr:String = formatter.format(date);
			var newDateStr:String = formatter.format(newDate);
			
			var query:String = "SELECT f_event_name, f_event_data, et.f_event_type, f_event_organiser, f_event_location, f_event_date from " +
				"t_events as evt INNER JOIN t_event_types as et on evt.f_event_type_id = et.f_event_type_id WHERE " +
				"STRFTIME('%Y-%m-%d', f_event_date) >=  STRFTIME('%Y-%m-%d', '" +dateStr+ "') AND " +
				"STRFTIME('%Y-%m-%d', f_event_date) <=  STRFTIME('%Y-%m-%d', '" +newDateStr+ "')";
			var result:SQLResult = executeQueryStatement(query);
			
			var eventsArray:Array = new Array();
			for each (var eventObj:Object in result.data)
			{
				eventsArray.push(new EventData(eventObj.f_event_name, eventObj.f_event_date, eventObj.f_event_type, eventObj.f_event_location, eventObj.f_event_organiser, eventObj.f_event_data));
			}
			
			return (new ListCollection(eventsArray));
		}
		
		public static function getAllJobs():ListCollection
		{
			var query:String = "SELECT * FROM t_jobs_postings";
			var result:SQLResult = executeQueryStatement(query);
			
			var jobPostingsArray:Array = new Array();
			for each(var jobObject:Object in result.data)
			{
				jobPostingsArray.push(new Job(jobObject.f_job_id, jobObject.f_job_title, jobObject.f_job_type, jobObject.f_job_exp, jobObject.f_job_description));
			}
			
			return (new ListCollection(jobPostingsArray));
		}
		
		public static function getUserEmail():String
		{
			var query:String = "SELECT f_isregistered_user, f_user_email FROM t_settings";
			var result:SQLResult = executeQueryStatement(query);
			
			return result.data[0].f_user_email;
		}
		
		public static function checkIsUserRegistered():Boolean
		{
			var query:String = "SELECT f_isregistered_user FROM t_settings";
			var result:SQLResult = executeQueryStatement(query);
			
			if(result.data[0].f_isregistered_user == 1)
			{
				return true;
			}
			
			else return false;
		}
	}
}